package architecture.base.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelResolve {
	private Workbook workbook;
	private InputStream inputStream;
	protected Field[] fields;
	//private List<Row> listRows;
	
	/**
	 * 生成Excel
	 * @param path
	 */
	public void generateDataSource(){
		//String filePathString = ServletActionContext.getServletContext().getRealPath("/");
	}
	
	/**
	 * 读取Excel
	 */
	public void readDataSource(String path){
		File file = new File(path);
		readDataSource(file);
	}
	
	public void readDataSource(File file){
		try {
			inputStream = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 生成excel需要先获得实体对象的属性值
	 * 通过实体类获取实体的各个属性的get set方法以便取得属性的值
	 * 遍历属性值之后用list保存 对应excel一行数据
	 * @param entity
	 * @return 
	 */
	public List<Object> getMetaDataByClass(Object entity){
		if(entity != null){
			fields = entity.getClass().getDeclaredFields();
			List<String> titles = new ArrayList<String>();
			List<Object> cellValueList = new ArrayList<Object>();
			for(Field field : fields){
				titles.add(field.getName());
				String methodName = "get" + StringUtils.capitalize(field.getName());
				try {
					Method method = entity.getClass().getDeclaredMethod(methodName,null);
					Object value = (Object) method.invoke(entity,null);
					cellValueList.add(value);
				} catch (NoSuchMethodException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SecurityException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}
			return cellValueList;
		}else {
			throw new NullPointerException("the parameter entity of method getMetaDataByClass is null");
		}
	}
	
	public List<Object> getMetaDataByClass(List<Object> objects){
		List<Object> cellValueList = new ArrayList<Object>();
		List<Object> rowValueList = new ArrayList<Object>();
		for(Object object : objects){
			cellValueList = getMetaDataByClass(object);
			rowValueList.add(cellValueList);
		}
		return rowValueList;
	}
	
	/**
	 * 设置excel表格样式
	 * @param rowList
	 */
	public void writeExcel(List<List<Object>> rowList){
		this.createWorkbook();
		Sheet sheet = workbook.createSheet("sheet");
		Row row = sheet.createRow(0);
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom((short) 1);
		cellStyle.setBorderLeft((short) 1);
		cellStyle.setBorderRight((short) 1);
		cellStyle.setBorderTop((short) 1);
		cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
		for(List<Object> cellList : rowList){
			for(Object obj : cellList){
				
			}
		}
	}
	
	/**
	 * 生成工作薄
	 */
	public void createWorkbook(){
		try {
			if(inputStream != null){
				new WorkbookFactory();
				workbook = WorkbookFactory.create(inputStream);
				getMetaDataByExcel();
			}else{
				workbook = new HSSFWorkbook();
			}
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 逐行取出单元格内数据，保存在list内
	 * @return
	 */
	public List<Cell> getMetaDataByExcel(){
		//createWorkbook();
//		List<String> columnNames = null;
		int rowCount = 0;
		for(int s = 0;s < workbook.getNumberOfSheets();s++){
			Sheet sheet = workbook.getSheetAt(s);
			rowCount = sheet.getPhysicalNumberOfRows();
			List<Object> listCells = new ArrayList<Object>();
			List<List<Object>> listRow = new ArrayList<List<Object>>();
			int cellCount = 0;
			for(int r = 0;r < rowCount;r++){
				Row row = sheet.getRow(r);
				cellCount = row.getPhysicalNumberOfCells();
				for(int c = 0;c < cellCount;c++){
					Cell cell = row.getCell(c);
					listCells.add(getCellValueObject(cell));
					System.out.print(getCellValueObject(cell) + " ");
				}
				listRow.add(listCells);
				
				System.out.println("\n");
			}
		}
		return null;
	}
	/**
	 * 取得cell对象的值
	 * @param cell
	 * @return
	 */
	public Object getCellValueObject(Cell cell){
		if(cell != null){
			int cellType = cell.getCellType();
			switch(cellType){
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_NUMERIC:
				return asInteger(cell.getNumericCellValue());
			case Cell.CELL_TYPE_BOOLEAN:
				return Boolean.valueOf(cell.getBooleanCellValue());
			case Cell.CELL_TYPE_BLANK:
				return "";
			 case Cell.CELL_TYPE_FORMULA:
		        int formulaResultType = cell.getCachedFormulaResultType ();
		        switch (formulaResultType)
		        {
		          case Cell.CELL_TYPE_NUMERIC:
		            return asInteger(cell.getNumericCellValue ());
		          case Cell.CELL_TYPE_STRING:
		            return cell.getStringCellValue ();
		          case Cell.CELL_TYPE_BOOLEAN:
		            return Boolean.valueOf (cell.getBooleanCellValue ());
		          default:
		            throw new IllegalArgumentException ("The cell formula type " + formulaResultType + " is unsupported!");
		        }
			default:
		        throw new IllegalArgumentException ("The cell type " + cellType + " is unsupported!");
			}
		}else{
			return "";
		}
	}
	
	private Integer asInteger(double cellValue){
			return Integer.valueOf((int) cellValue);
	}
	
	public Workbook getWorkbook() {
		return workbook;
	}
	
}
